package beyjava.dbUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

/**
 * this class is used for running database commands
 * @author BEYJAVA
 *
 */
public class DbUtility {
	private static DbUtility dbInstance = null;
	/**
	 * @return DbUtility
	 */
	public  static synchronized DbUtility getInstance(){
		if(dbInstance == null){
			  dbInstance = new DbUtility();
		}
		return dbInstance;
	}
	private List<Map<String, Object>> executeQueryMap(Connection conn ,String query, Object[] param){
		QueryRunner qrunner = new QueryRunner();
		List<Map<String, Object>> maplist = null;
		try {
			 maplist  =  qrunner.query(conn, query,param, new MapListHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(e.getMessage());
		}
		
		return maplist;
	}
    private List<Map<String, Object>> executeQueryMap(Connection conn,String query){
		QueryRunner  qrunner = new QueryRunner();
		List<Map<String, Object>> mapList = null;
		try{
			mapList = (List<Map<String, Object>>)qrunner.query(conn, query, new MapListHandler());
		}catch(SQLException e){
			e.printStackTrace();
		}
		
		return mapList;
	}
	private  int updateQuery(Connection conn , String query ,Object[] params){
		int insertedrow = 0;
		QueryRunner qrunner = new QueryRunner();
		try {
			insertedrow = qrunner.update(conn, query,params);
					} catch (SQLException e) {
		
			e.printStackTrace();
		}
		return insertedrow;
	}
	private int updateQuery(Connection conn , String query){
		int insertedRow = 0;
		QueryRunner qrunner = new QueryRunner();
		try {
			insertedRow = qrunner.update(conn, query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	  return insertedRow;
	}
	private int getGeneratedRowId(Connection conn){
		Long generatedRow = null ;
		QueryRunner qrunner = new QueryRunner();
		try {
			generatedRow  = (Long) qrunner.query (conn, "SELECT LAST_INSERT_ID ()", new ScalarHandler (1));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
       return generatedRow.intValue();
	}
	/**
	 * This method provides execute select command  w for database
	 * @param query
	 * @return List the rows of data for given query
	 */
	public List<Map<String,Object>> execute(String query){
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection conn = cm.getConnection();	
		List<Map<String,Object>> list =	executeQueryMap(conn, query);
	    cm.closeConnection(conn);
	    return list;
	   
	}
	/**
	 * This method provides execute select command  with parameters for database 
	 * @param query
	 * @param param
	 * @return List the rows of data for given query
	 */
	public List<Map<String,Object>> execute(String query, Object[] param){
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection conn = cm.getConnection();	
	    List<Map<String,Object>> list =	executeQueryMap(conn, query,param);
	    cm.closeConnection(conn);
	    return list;
	}
	/**
	 * This method provide update  'insert,delete,update command for database'
	 * @param query
	 * @return a the number of effected rows  
	 * 
	 */
	public int update (String query){
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection conn =  cm.getConnection();
		int rowId = updateQuery(conn, query);
		cm.closeConnection(conn);
		return rowId;
	}
	/**
	 * This method provide update  'insert,delete,update command  with parameters for database'
	 * @param query
	 * @return a the number of effected rows  
	 * 
	 */
	public int update (String query, Object[] params){
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection conn =  cm.getConnection();
		int rowId = updateQuery(conn, query,params);
		cm.closeConnection(conn);
		return rowId;
	}
	/**
	 * @return int number of effected rows
	 */
	public int  insertUpdate(){
        ConnectionManager cm = ConnectionManager.getInstance();
	    Connection conn =  cm.getConnection();
	    int generatedKey = getGeneratedRowId(conn);
	    cm.closeConnection(conn);
	    return generatedKey;
	}
}

